package com.watering.plan;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Calendar;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

/*
 *  The class that deals with the interaction with the
 *  database and all the tables, creating the database,
 *  opening and executing Sql commands acording to the
 *  needs of the other classes.
 */

public class Base extends SQLiteOpenHelper{
 
    private static String DB_PATH = "/data/data/com.watering.plan/databases/";
    private static String DB_NAME = "WateringPlan";
    private SQLiteDatabase myDataBase; 
    private final Context myContext;
    Calendar cal;
 
    public Base(Context context) {
 
    	super(context, DB_NAME, null, 1);
        this.myContext = context;
    }	
 

    public void createDataBase() throws IOException{
 
    	boolean dbExist = checkDataBase();
 
    	if(dbExist){
    		//do nothing - database already exist
    	}else{
    		//creates new database in default path.
        	this.getReadableDatabase();

        	try {
    			copyDataBase();
    		} catch (IOException e) {
 
        		throw new Error("Error copying database");
 
        	}
    	}
 
    }
 
    // Checking if the database already exists, returns true if it does
    // Avoids creating a new database when one is already present.
    private boolean checkDataBase(){
 
    	SQLiteDatabase checkDB = null;
 
    	try{
    		String myPath = DB_PATH + DB_NAME;
    		checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
 
    	}catch(SQLiteException e){ }
 
    	if(checkDB != null)
    		checkDB.close();
 
    	return checkDB != null ? true : false;
    }
 
    /**
     * Copies your database from your local assets-folder to the just created empty database in the
     * system folder, from where it can be accessed and handled.
     * This is done by transfering bytestream.
     * */

    private void copyDataBase() throws IOException{
 
    	InputStream myInput = myContext.getAssets().open(DB_NAME);
    	String outFileName = DB_PATH + DB_NAME;
    	OutputStream myOutput = new FileOutputStream(outFileName);
 
    	byte[] buffer = new byte[1024];
    	int length;
    	while ((length = myInput.read(buffer))>0){
    		myOutput.write(buffer, 0, length);
    	}
 
    	myOutput.flush();
    	myOutput.close();
    	myInput.close();
 
    }
 
    public void openDataBase() throws SQLException{
 
        String myPath = DB_PATH + DB_NAME;
    	myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
    }
 
    @Override
	public synchronized void close() {
 
    	    if(myDataBase != null)
    		    myDataBase.close();
 
    	    super.close();
	}
 
	@Override
	public void onCreate(SQLiteDatabase db) {

	}
 
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
	}
	
	public void insertLarge(String sqlStatement) throws SQLException{
		myDataBase.execSQL(sqlStatement);
		
	}
	
	public void insertDataMain(	String commonName, String latinName ,
			String plantType, String description,
			String origin, String flower,
			String season, String easeGrow,
			String temperature, String humidity,
			String light, String soil,
			String water, String fertilizer, String nothing){

			String TableName = "PlantInfoLarge";
			myDataBase.execSQL("INSERT INTO " + TableName +
			" VALUES ( NULL ,"
			+ "'"+ commonName + "'" + ","+ "'" + latinName+ "'" + ","+ "'" + plantType+ "'" + ","+ "'" + description+ "'"
			+ ","+ "'" + origin+ "'" + ","+ "'" + flower+ "'" + ","+ "'" + season+ "'"  + ","+ "'" + easeGrow+ "'"  + "," + "'"+ temperature+ "'"  + ","+ "'" + humidity+ "'"  + ","+ "'" + light+ "'"  + ","+ "'" + soil+ "'"  + ","+ "'" + water+ "'" + ","+ "'" + fertilizer+ "'" + ");");

}
	
	// WATERING FORMULA
	public void insertRegistered(String plantName,String plantType, int pot,int fol ,int light){
	//	Cursor c = myDataBase.rawQuery("SELECT * FROM " + "PlantInfoLarge" + " where latinName like '%"+ plantType + "%';", null);
	//	if( c.getCount() <= 0 )
	//		c = myDataBase.rawQuery("SELECT * FROM " + "PlantInfoLarge" + " where commonName like '%"+ plantType + "%';", null);
		
	//	if( c.getCount() <= 0)
		//	return;
		
		double water = pot/2 + 0.33;
		if( fol < 3 )
			water = water - water*(4/100);
		else{
			if( fol < 5 )
				water = water - water*(2/100);
			else
				water = water + water*(3/100);
		}
		
		if( light < 3)
			water = water - water*(4/100);
		else
			if( light > 7 )
				water = water + water*(3/100);
		int no_days;
		// light will be used for no of days
		String cat = "";
		String humid = "";
	//	if( c.getCount() > 0){
	//		cat = c.getString(4);
	//		humid = c.getString(11);
		//}else {
			cat = "Shrub";
			humid = "Medium Humidity";
			
	//	}
		if(humid.contains("Low"))
			{no_days = 4; water = (water + water*(10/100))/10;}
		else{
			if( humid.contains("Medium")){
				no_days = 3;  
			}
			else{
				no_days = 2;
				water = water - water*(30/100);
			}
		}
		cal = Calendar.getInstance();
		String date =  cal.DAY_OF_MONTH + ":" + cal.MONTH ;
		myDataBase.execSQL("INSERT INTO " + "PlantRegistered" +
				" VALUES ( NULL ,"
				+ "'"+ plantName + "'" + ","+ "'" + plantType+ "'" + ","+ "'" + cat + "'" + ","+ "'" + pot + "'"
				+ ","+ "'" + fol + "'" + ","+ "'" + no_days+ "'" + ","+ "'" + water + "'"  + ","+ "'" + date + "'" + ");" );
	//	myDataBase.execSQL(INSERT INTO Schedule VALUES( +'plantName' ,NULL));
			
		
	}
	
	public ArrayList<String> getPlantArray(){
		ArrayList<String> plants = new ArrayList();

		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "PlantInfoLarge", null);		
		c.moveToFirst();
		
		if (c != null) {
			do {
				String f1 = c.getString(2);
				String f2 = c.getString(3);
				plants.add(f1);
				if( f2.contains("null") != true )
					plants.add(f2);
			} while (c.moveToNext());
		}
		return plants;
	}
	
	public ArrayList<String[]> getRegList(){
		ArrayList<String[]> plants = new ArrayList();
		
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "PlantRegistered", null);		
		if (c.getCount() > 0) {
			do {
				String name = c.getString(2);
				String nodays = c.getString(7);
				String last = c.getString(9);
				String f1[] = {name,nodays,last};
				plants.add(f1);
			} while (c.moveToNext());
		}
		return plants;
		
	}
	
	public String getReg(){
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "PlantRegistered", null);		
		c.moveToFirst();
		String man = "";
		if( c.getCount() > 0 ){
			do {
				man  += c.getString(1)+ ",";
				man  += c.getString(6)+ ",";
				man  += "10:10" + ";";
			} while (c.moveToNext());
		}else {
			man = "nimic";		
		}
		return man;
	}
	
	public String getPlantReg(String plantName){
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "PlantRegistered" + " where PlantName like '%"+ plantName + "%';", null);
		c.moveToFirst();
		String man = "";
		if( c.getCount() > 0 ){
			int nrc = c.getColumnCount();
			int i;
			for( i = 2; i < nrc; i++ ){
				//man = man + c.getString(i)+ "\n"+ "\n";
			if( i == 2 )
				man = man + "Plant Type: "+ c.getString(i)+ "\n"+ "\n";
			if( i == 3)
				man = man + "Plant Category: "+ c.getString(i)+ "\n"+ "\n";
			if( i == 4)
				man = man + "Pot Size: "+ c.getString(i)+ "\n"+ "\n";
			if( i == 5)
				man = man + "Foliage Size: "+ c.getString(i)+ "\n"+ "\n";
			if( i == 7)
				man = man + " Water: "+ c.getString(i)+ "\n"+ "\n";
			if( i == 8)
				man = man + "Last Watering: "+ c.getString(i)+ "\n"+ "\n";
			}
				
		}
		return man;
	}
	
	public String getPlantInfo(String plantName){
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "PlantInfoLarge" + " where latinName like '%"+ plantName + "%';", null);
		c.moveToFirst();
		String man = "";
		if( c.getCount() > 0 ){
			int nrc = c.getColumnCount();
			int i;
			for( i = 1; i < nrc; i++ ){
				//man = man + c.getString(i)+ "\n"+ "\n";
				 if( i == 2)
				    man = man + "Common Name: \n" +c.getString(i)+ "\n"+ "\n";
				 if( i == 3)
					    man = man + "Latin Name: \n" +c.getString(i)+ "\n"+ "\n";
				 if( i == 4)
					    man = man + "Plant Name: \n" +c.getString(i)+ "\n"+ "\n";
				 if( i == 5)
					    man = man + "Description: \n" +c.getString(i)+ "\n"+ "\n";
				 if( i == 6)
					    man = man + "Origin: \n" +c.getString(i)+ "\n"+ "\n";
				 if( i == 7)
					    man = man + "Flower: \n" +c.getString(i)+ "\n"+ "\n";
				 if( i == 8)
					    man = man + "Season: \n" +c.getString(i)+ "\n"+ "\n";
				 if( i == 9)
					    man = man + "Ease of Growth: \n" +c.getString(i)+ "\n"+ "\n";
				 if( i == 10)
					    man = man + "Temperature: \n" +c.getString(i)+ "\n"+ "\n";
				 if( i == 11)
					    man = man + "Humidity: \n" +c.getString(i)+ "\n"+ "\n";
			}
		}
		else{
			c = myDataBase.rawQuery("SELECT * FROM " + "PlantInfoLarge" + " where commonName like '%"+ plantName + "%';", null);
			c.moveToFirst();
			if( c != null ){
				int nrc = c.getColumnCount();
				int i;
				for( i = 1; i < nrc; i++ ){
					 if( i == 2)
						    man = man + "Common Name: \n" +c.getString(i)+ "\n"+ "\n";
						 if( i == 3)
							    man = man + "Latin Name: \n" +c.getString(i)+ "\n"+ "\n";
						 if( i == 4)
							    man = man + "Plant Name: \n" +c.getString(i)+ "\n"+ "\n";
						 if( i == 5)
							    man = man + "Description: \n" +c.getString(i)+ "\n"+ "\n";
						 if( i == 6)
							    man = man + "Origin: \n" +c.getString(i)+ "\n"+ "\n";
						 if( i == 7)
							    man = man + "Flower: \n" +c.getString(i)+ "\n"+ "\n";
						 if( i == 8)
							    man = man + "Season: \n" +c.getString(i)+ "\n"+ "\n";
						 if( i == 9)
							    man = man + "Ease of Growth: \n" +c.getString(i)+ "\n"+ "\n";
						 if( i == 10)
							    man = man + "Temperature: \n" +c.getString(i)+ "\n"+ "\n";
						 if( i == 11)
							    man = man + "Humidity: \n" +c.getString(i)+ "\n"+ "\n";
			}
		  }
		}
		return man;
	}
	
	public void addRestriction(int id,int hour_s,int min_s,int hour_f,int min_f,int day_s,int month_s,int day_f,int month_f){
		myDataBase.execSQL("INSERT INTO " + "Restrictions" +
				" VALUES ( NULL ," + hour_s + ","+ min_s + ","+ hour_f + "," + min_f + ","+ day_s + "," + month_s + "," + day_f + "," + month_f + " );" );
		
	}
	
	public String getRestriction(){
		String man = "";
		String newline = System.getProperty("line.separator");
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "Restrictions", null);

		c.moveToFirst();
		
		if (c != null) {
			// Loop through all Results
			do {
				String f1 = c.getString(1);
				String f2 = c.getString(2);
				man = man + f1 + " " + f2 + newline;
			} while (c.moveToNext());
		}
		return man;
	}
	
	// Adding notification to code
	public void addNotify( int day ){
		myDataBase.execSQL("INSERT INTO " + "Notifications" +
				" VALUES ( NULL, " + day + " NULL,NULL,NULL);" );
		
	}
/*	
	public void updateReg(){
		Calendar cal = Calendar.getInstance();
		int day = cal.get(Calendar.DAY_OF_MONTH);
		int month = cal.get(Calendar.MONTH);
		Cursor c = myDataBase.rawQuery("Select * from PlantRegistered;",null);
		c.moveToFirst();
		
		myDataBase.rawQuery("Delete * from PlantRegistered", null);
	
		if( c.getCount() == 0 )
			return ;
		else{
			int nr = c.getCount();
			int i;
			for( i = 0 ; i < nr; i++ ){
				int id = c.getInt(0);
				String PlantName = c.getString(1);
				String PlantType = c.getString(2);
				String PlantCat = c.getString(3);
				int potSize = c.getInt(4);
				int FoliageSize =c.getInt(5);
				int Light = c.getInt(6);
				int water = c.getInt(7);
				String lastWatering = c.getString(8);
				
				
				
				
		//		myDataBase.execSQL("INSERT INTO " + "PlantRegistered" +
		//		" VALUES (" + id + "," + PlantName + "," + PlantType PlantCat potSize
		//		FoliageSize Light water lastWatering" );
			}
		}
		
	}
	*/
	
	public String getNextNotify(){
		String next = "";
		Cursor c = myDataBase.rawQuery("Select * from Notifications Where day like (Select min(day) from Notifications group by Plant);",null);
		
		c.moveToFirst();
		
		if( c != null )
			next = "not null";
		else
			next = "null";
		
		return next;
		
	}
	
	
	
	
	
	public String getList()throws Exception{
		String man = "";
		String newline = System.getProperty("line.separator");
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "PlantInfoLarge", null);
		//int c1 = c.getColumnIndex("commonName");
		//int c2 = c.getColumnIndex("latinName");
		
		c.moveToFirst();
		
		if (c != null) {
			man = "prima";
			// Loop through all Results
			do {
				String f1 = c.getString(1);
				String f2 = c.getString(2);
				man = man + f1 + " " + f2 + newline;
			} while (c.moveToNext());
		}
		return man;
	}
	
 
}